﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace MailSharp
{
  public class DatabaseHelper
  {


    public static void ArchiveMessage(IDbConnection conn, RecipientInfo recipient, SenderInfo sender, TemplateInfo template, bool success)
    {
      IDbCommand cmd = CreateCommand(conn, "INSERT INTO mailsharp_message_archive " +
                                           "(from_name, from_email, sender, reply_to, subject, html_body, plain_text_body) " +
                                           "VALUES " +
                                           "(@from_name, @from_email, @sender, @reply_to, @subject, @html_body, @plain_text_body)");

      AddStatementTerminator(cmd);
      AddLastIdStatement(cmd);

      AddParameter(cmd, "@from_name", sender.FromName);
      AddParameter(cmd, "@from_email", sender.FromEmail);
      AddParameter(cmd, "@sender", sender.Sender);
      AddParameter(cmd, "@reply_to", sender.ReplyTo);
      AddParameter(cmd, "@subject", "");
      AddParameter(cmd, "@html_body", template.Content);
      AddParameter(cmd, "@plain_text_body", "");
      cmd.Connection.Open();
      object newIdObject = cmd.ExecuteScalar();
      long newId = -1;
      if (newIdObject is long || newIdObject is int)
        newId = (long)newIdObject;
      else
        System.Diagnostics.Debug.WriteLine(newIdObject.GetType().ToString());

      cmd.Connection.Close();

      cmd = CreateCommand(conn, "INSERT INTO mailsharp_recipient_archive " +
                                "(message_id, to_name, to_email, merge_values, success, send_date_time) " +
                                "VALUES " +
                                "(@message_id, @to_name, @to_email, @merge_values, @success, @send_date_time)");
      AddParameter(cmd, "@message_id", newId);
      AddParameter(cmd, "@to_name", recipient.RecipientName);
      AddParameter(cmd, "@to_email", recipient.RecipientEmail);
      AddParameter(cmd, "@merge_values", DictionaryToString(recipient.MergeValues));
      AddParameter(cmd, "@success", success ? 1 : 0);
      AddParameter(cmd, "@send_date_time", DateTime.Now);
      cmd.Connection.Open();
      cmd.ExecuteNonQuery();
      cmd.Connection.Close();

    }

    private static string DictionaryToString(Dictionary<string, string> d)
    {
      string retVal = "";
      if (d == null) return retVal;

      foreach (var tk in d.Keys)
      {
        retVal += tk.ToString() + "=" + d[tk].ToString() + "&";
      }
      if (retVal.Length > 0) retVal = retVal.Substring(0, retVal.Length - 1);

      return retVal;
    }

    private static void AddParameter(IDbCommand cmd, string parameterName, object value)
    {
      IDbDataParameter p = cmd.CreateParameter();
      p.ParameterName = parameterName;
      p.Value = value;
      cmd.Parameters.Add(p);
    }

    public static void ArchiveMessageRecipient()
    {
    }

    public static void ScheduleMessage()
    {
    }

    private static void AddLastIdStatement(IDbCommand cmd)
    {
      if (cmd is System.Data.SqlClient.SqlCommand)
        cmd.CommandText += "SELECT @@IDENTITY\nGO\n";
      else if (cmd is MySql.Data.MySqlClient.MySqlCommand)
        cmd.CommandText += "SELECT last_insert_id();\n";
      else
        throw new Exception("Database not supported: " + cmd.GetType().Name);
    }

    private static void AddStatementTerminator(IDbCommand cmd)
    {
      if (cmd is System.Data.SqlClient.SqlCommand)
        cmd.CommandText += System.Environment.NewLine + "GO" + System.Environment.NewLine;
      else if (cmd is MySql.Data.MySqlClient.MySqlCommand)
        cmd.CommandText += ";" + System.Environment.NewLine;
      else
        throw new Exception("Database not supported: " + cmd.GetType().Name);
    }


    private static IDbCommand CreateCommand(IDbConnection connection, string text)
    {
      IDbCommand retVal;
      if (connection is System.Data.SqlClient.SqlConnection)
        retVal = new System.Data.SqlClient.SqlCommand();
      else if (connection is MySql.Data.MySqlClient.MySqlConnection)
        retVal = new MySql.Data.MySqlClient.MySqlCommand();
      else
        throw new Exception("Database not supported: " + connection.GetType().Name);

      retVal.Connection = connection;
      retVal.CommandType = CommandType.Text;
      retVal.CommandText = text;

      return retVal;
    }

  }
}
